package jdbc;

/**
 *
 * @author Owner
 */
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

public class ConexaoJdbc {
	// JDBC driver name and database URL
	public static final int MOTOR_MYSQL = 0, MOTOR_ORACLE = MOTOR_MYSQL + 1,
			MOTOR_POSTGRESQL = MOTOR_ORACLE + 1, MOTOR_SQLSERVER = MOTOR_POSTGRESQL + 1,
			MOTOR_FIREBIRD = MOTOR_SQLSERVER + 1;

	private String jdbcDriver[] = { "com.mysql.jdbc.Driver",
			"oracle.jdbc.driver.OracleDriver", "org.postgresql.Driver",
			"com.microsoft.jdbc.sqlserver.SQLServerDriver",
			"org.firebirdsql.jdbc.FBDriver" };

	private String dbUrl[] = {
			"jdbc:mysql://",
			"jdbc:oracle:thin:@",
			"jdbc:postgresql://",
			"jdbc:microsoft:sqlserver://",
			"jdbc:firebirdsql:" };

	private String dbUrl2[] = {
			"jdbc:mysql://localhost:3306/",
			"jdbc:oracle:thin:@10.1.0.123:1521:",
			"jdbc:postgresql://localhost:5432/",
			"jdbc:microsoft:sqlserver://localhost:1433/",
			"jdbc:firebirdsql:localhost:" };

	//jdbc:oracle:thin:@10.1.0.123:1521:TIATST
	// 10.5.1.201 - dex
	
	// acesso a base de dados
	private String dbHost;
	private String dbPort;
	private String dbName;
	private String dbUser;
	private String dbPwd;
	// manages connection
	private Connection connection;
	private PreparedStatement preparedStatement;
	private Statement statement;
	// query dbName
	private ResultSet resultSet;
	// process query results
	private ResultSetMetaData resultSetMetaData;

	public ConexaoJdbc() {
		dbHost = "";
		dbPort = "";
		dbName = "";
		dbUser = "";
		dbPwd = "";
	}

	public ConexaoJdbc(String dbHost, String dbPort, String dbName, String dbUser, String dbPwd, int motor ) {
		this.dbHost = dbHost;
		this.dbPort = dbPort;
		this.dbName = dbName;
		this.dbUser = dbUser;
		this.dbPwd = dbPwd;
		
		switch (motor) {
		case MOTOR_MYSQL: this.criarConexaoMySQL(); break;
		case MOTOR_ORACLE: this.criarConexaoOracle(); break;			
		case MOTOR_POSTGRESQL: this.criarConexaoPostgreSQL(); break;
		case MOTOR_SQLSERVER: this.criarConexaoSQLServer(); break;			
		case MOTOR_FIREBIRD: this.criarConexaoFirebird(); break;
		}
	}

	private void criarConexaoMySQL() {
		try {
			Class.forName(jdbcDriver[MOTOR_MYSQL]);
			connection = DriverManager.getConnection(dbUrl[MOTOR_MYSQL]+dbHost+":"+dbPort+"/"+dbName, dbUser, dbPwd);
			
			// Require manual commit for transactions. This enables
			// the program to rollback transactions that do not
			// complete and commit transactions that complete properly.
			connection.setAutoCommit( false );
			criarStatment();
		} catch (ClassNotFoundException ex) {
			ex.printStackTrace();
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
	}	
	
	private void criarConexaoOracle() {
		try {
			Class.forName(jdbcDriver[MOTOR_ORACLE]);
			connection = DriverManager.getConnection(dbUrl[MOTOR_ORACLE]
					+dbHost+":"+dbPort+":"+dbName, dbUser, dbPwd);
			
			// Require manual commit for transactions. This enables
			// the program to rollback transactions that do not
			// complete and commit transactions that complete properly.
			connection.setAutoCommit( false );
			criarStatment();
		} catch (ClassNotFoundException ex) {
			ex.printStackTrace();
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
	}

	private void criarConexaoPostgreSQL() {
		try {
			Class.forName(jdbcDriver[MOTOR_POSTGRESQL]);
			connection = DriverManager.getConnection(dbUrl[MOTOR_POSTGRESQL]
					+dbHost+":"+dbPort+"/"+ dbName, dbUser, dbPwd);
			
			// Require manual commit for transactions. This enables
			// the program to rollback transactions that do not
			// complete and commit transactions that complete properly.
			connection.setAutoCommit( false );
			criarStatment();
		} catch (ClassNotFoundException ex) {
			ex.printStackTrace();
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
	}
	
	private void criarConexaoSQLServer() {
		try {
			Class.forName(jdbcDriver[MOTOR_SQLSERVER]);
			connection = DriverManager.getConnection(dbUrl[MOTOR_SQLSERVER]
					+dbHost+":"+dbPort+"/"+ dbName, " User=" + dbUser, " Password=" + dbPwd);
			
			// Require manual commit for transactions. This enables
			// the program to rollback transactions that do not
			// complete and commit transactions that complete properly.
			connection.setAutoCommit( false );
			criarStatment();
		} catch (ClassNotFoundException ex) {
			ex.printStackTrace();
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
	}

	private void criarConexaoFirebird() {
		try {
			Class.forName(jdbcDriver[MOTOR_FIREBIRD]);
			connection = DriverManager.getConnection(dbUrl[MOTOR_FIREBIRD]
					+dbHost+":"+ dbName, dbUser, dbPwd);
			
			// Require manual commit for transactions. This enables
			// the program to rollback transactions that do not
			// complete and commit transactions that complete properly.
			connection.setAutoCommit( false );
			criarStatment();
		} catch (ClassNotFoundException ex) {
			ex.printStackTrace();
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
	}

	/**
	 * Cria um PreparedStatment para consultas na base de dados.
	 * 
	 * @param sql
	 * @throws SQLException
	 */
	public void criarPreparedStatment(String sql) {
		try {
			preparedStatement = connection.prepareStatement(sql,
					ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
	}

	/**
	 * Cria um Statment para consultas na base de dados.
	 * 
	 * @throws SQLException
	 */
	public void criarStatment() throws SQLException {
		statement = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
				ResultSet.CONCUR_UPDATABLE);
	}

	/**
	 * Executa a query do argumento numa conexao aberta
	 * 
	 * @param query
	 * @return resultSet
	 */
	public ResultSet executarQueryComStatement(String query) {
		try {
			resultSet = statement.executeQuery(query);
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
		return resultSet;
	}

	/**
	 * Executa uma actualizacao numa conexao aberta atraves dum Statement.
	 * 
	 * @param sqlInsert
	 * @return resultado
	 */
	public int executarUpdateComStatement(String sqlInsert) {
		int resultado = -1;
		try {
			resultado = statement.executeUpdate(sqlInsert);
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
		return resultado;
	}

	/**
	 * Executa a query numa conexao aberta para um PreparedStatement previamente
	 * criado.
	 */
	public void executarQueryComPreparedStatement() {
		try {
			resultSet = preparedStatement.executeQuery();
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
	}

	/**
	 * Executa uma actualizacao numa conexao aberta para um PreparedStatement
	 * previamente criado.
	 * 
	 * @return resultado
	 */
	public int executarUpdateComPreparedStatement() {
		int resultado = 0;
		try {
			resultado = preparedStatement.executeUpdate();
		} catch (SQLException ex) {
			// ex.printStackTrace();
			return resultado;
		}
		return resultado;
	}

	
	public void commitTransaction() {
		try {
			connection.commit(); // commit update
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
	}
	
	public void rollbackTransaction() {
		try {
			connection.rollback(); // rollback update
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
	}

	/**
	 * Configura ResultSetMetaData
	 * 
	 * @throws SQLException
	 */
	public void setMetaData() {
		try {
			resultSetMetaData = preparedStatement.getMetaData();
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
	}

	/**
	 * Retorna um objecto Connection.
	 * 
	 * @return connection
	 */
	public Connection getConnection() {
		return connection;
	}

	/**
	 * Retorna um PreparedStatement.
	 * 
	 * @return preparedStatement
	 */
	public PreparedStatement getPreparedStatement() {
		return preparedStatement;
	}

	/**
	 * Returna um ResultSet
	 * 
	 * @return resultSet
	 */
	public ResultSet getResultSet() {
		return resultSet;
	}

	/**
	 * Retorna um ResultSetMetaData, i.e., a definicao de dados.
	 * 
	 * @return resultSetMetaData
	 */
	public ResultSetMetaData getResultSetMetaData() {
		return resultSetMetaData;
	}

	/**
	 * Retorna o maior numero de um campo da tabela se ele for um inteiro.
	 * Assim, nao teremos nenhum ID igual a outro.
	 * 
	 * @param tabela
	 * @param campo
	 * @return campo
	 */
	public int getProximoIDMaximo(String tabela, String campo) {
		int id = 0;
		try {
			String sql = "SELECT max(" + campo + ") AS " + campo + " FROM "
					+ tabela;
			resultSet = this.executarQueryComStatement(sql);
			if (resultSet.next()) {
				id = resultSet.getInt(campo) + 1;
			}
		} catch (SQLException ex) {
			System.out.println("Erro na seleccao do ID Maximo.");
			ex.printStackTrace();
		}
		return id;
	}

	/**
	 * Assegura que as instatementancias resultSet, preparedStatement e conexao
	 * serao encerrados devidamente.
	 */
	public void fecharConexao() {
		try {
			if (resultSet != null) {
				resultSet.close();
				resultSet = null;
			}
			if (statement != null) {
				statement.close();
				statement = null;
			}
			if (preparedStatement != null) {
				preparedStatement.close();
				preparedStatement = null;
			}
			if (connection != null) {
				connection.close();
				connection = null;
			}

		} catch (Exception ex) {
			System.out.println("MyDBDriver - Erro ao encerrar a conexao.");
			ex.printStackTrace();
		}
	}

	/**
	 * @return the statement
	 */
	public Statement getStatement() {
		return statement;
	}

	/**
	 * @param statement the statement to set
	 */
	public void setStatement(Statement statement) {
		this.statement = statement;
	}

	/**
	 * @param connection the connection to set
	 */
	public void setConnection(Connection connection) {
		this.connection = connection;
	}

	/**
	 * @param preparedStatement the preparedStatement to set
	 */
	public void setPreparedStatement(PreparedStatement preparedStatement) {
		this.preparedStatement = preparedStatement;
	}

	/**
	 * @param resultSet the resultSet to set
	 */
	public void setResultSet(ResultSet resultSet) {
		this.resultSet = resultSet;
	}

	/**
	 * @param resultSetMetaData the resultSetMetaData to set
	 */
	public void setResultSetMetaData(ResultSetMetaData resultSetMetaData) {
		this.resultSetMetaData = resultSetMetaData;
	}
	
}